/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Dr.Tree
 * Created On : Jun 8, 2012  7:51:04 PM 
 * e-Mail : chatree@tis.co.th
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : apps.promis.in.construction
 * File Name : SupplierDebitNoteServicesAction.java
 * TODO
 */
package apps.promis.in.construction;

import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import jpa.entitys.promis.common.CostProject;
import jpa.entitys.promis.construction.SupplierDbnoteBudget;
import jpa.entitys.promis.construction.SupplierDbnoteBudgetPK;
import jpa.entitys.promis.construction.SupplierDebitNote;
import jpa.entitys.promis.construction.SupplierDebitNotePK;
import jpa.entitys.promis.construction.VwSyncGrnItem;
import jpa.entitys.synchro.staging.MmSuppdebitnotesinb;
import jpa.service.promis.common.ProjectServices;
import jpa.service.promis.construction.AccountPayableVoucherServices;
import jpa.service.promis.construction.SupplierDbnoteBudgetServices;
import jpa.service.promis.construction.SupplierDebitNoteServices;
import jpa.service.promis.construction.VwSyncGrnItemServices;
import jpa.service.synchro.staging.MmSuppdebitnotesinbServices;
import org.apache.log4j.Logger;
import promis.utility.CommonService;
import promis.utility.Utilitys;
import apps.sessionFactory;

/**
 * 
 * @author Chatree Siangklom of Administrator
 * Date =Nov 13, 2013 : 2:42:23 PM 
 * CreateBy=Chatree Siangklom
 * 
 * SupplierDebitNoteServicesAction
 * TODO
 */
public class SupplierDebitNoteServicesAction {
	
	private static final Logger log = Logger.getLogger(AccountPayableVoucherAction.class);
	static MmSuppdebitnotesinbServices  MmSuppdebitnotesinbServ = null;
	static EntityManager  EmPromis;
	static EntityManager  EmSynchro;

	/**
	 * 
	 * Date =Nov 13, 2013 : 2:42:27 PM 
	 * CreateBy=Chatree Siangklom
	 * SupplierDebitNoteServicesAction
	 * TODO
	 * @throws Exception
	 */
	public SupplierDebitNoteServicesAction() throws Exception {
		EmPromis = sessionFactory.CreateEmPromis();
		EmSynchro = sessionFactory.CreateEmSynchro();
		MmSuppdebitnotesinbServ = new MmSuppdebitnotesinbServices(this.EmSynchro);
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 2:42:32 PM 
	 * CreateBy=Chatree Siangklom
	 * PurchaseModel
	 * TODO
	 * @param company
	 * @param project
	 * @param po_document_no
	 * @return
	 * @throws Exception
	 */
	private PurchaseModel findPurchase(String company, String project, String po_document_no) throws Exception {
		try{
			StringBuffer _strQuery = new StringBuffer();

			_strQuery.append(" SELECT ");
			_strQuery.append(" COMPANY, PROJECT, PO_TYPE, PO_SERIES, PO_NO, NVL(REVISION_NO,0) AS REVISION_NO , ");
			_strQuery.append(" get_document_ref_no(PO_TYPE,PO_SERIES,PO_NO,REVISION_NO,'-') AS DOCUMENT_NO, STATUS ");
			_strQuery.append(" FROM PURCHASE_ORDERS ");
			_strQuery.append(" WHERE 1 = 1 ");  
			_strQuery.append(" AND COMPANY = ").append(Utilitys.strSql(company));
			_strQuery.append(" AND PROJECT = ").append(Utilitys.strSql(project));
			_strQuery.append(" AND SYNC_EXTERNAL_REF_NO = "+ Utilitys.strSql(po_document_no));
			_strQuery.append(" AND STATUS <> 'Z' ");
			log.info(CommonService.ReplaceStringToTxtDatabase(_strQuery.toString()));
			
			List<Object[]> result = (List<Object[]>)EmPromis.createNativeQuery(_strQuery.toString()).getResultList();
			 List<PurchaseModel> lst = new ArrayList<PurchaseModel>();
			 if(CommonService.getSize(result) > 0){				 
				 for (Object[] resultElement : result) {
					 //String company = CommonService.IsNullRString(resultElement[0]);
					 //String project = CommonService.IsNullRString(resultElement[1]);
					 String poType = CommonService.IsNullRString(resultElement[2]);
					 String poSeries = CommonService.IsNullRString(resultElement[3]);
					 String poNo = CommonService.IsNullRString(resultElement[4]);
					 String revisionNo = CommonService.IsNullRString(resultElement[5]);
					 String documentNo = CommonService.IsNullRString(resultElement[6]);
					 String status = CommonService.IsNullRString(resultElement[7]);
					 
					 return new PurchaseModel(company, project, poType, poSeries, CommonService.ConvertStringToBigDecimal(poNo), CommonService.ConvertStringToBigDecimal(revisionNo), documentNo, status);
				 }
			 }else{
				 System.out.println("Purchase Order No is [Nothing. SAP No : " + po_document_no + ", company:"+ company + ", project="+ project +"]");
				 log.error("Purchase Order No is [Nothing. SAP No : " + po_document_no + ", company:"+ company + ", project="+ project +"]");
				 throw new Exception("Purchase Order No is [Nothing. SAP No : " + po_document_no + ", company:"+ company + ", project="+ project +"]");
			 }
		}catch(Exception e){
			log.error(e.getMessage());
			System.err.println(e.getMessage());
			throw e;
		}
		return null;
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 2:42:34 PM 
	 * CreateBy=Chatree Siangklom
	 * VwSyncGrnItem
	 * TODO
	 * @param company
	 * @param project
	 * @param documentNo
	 * @param unitNumber
	 * @param status
	 * @return
	 * @throws Exception
	 */
	private VwSyncGrnItem FindGRNBudgetByUnit(String company, String project ,String documentNo, String unitNumber, String status) throws Exception {
		try{
			
			VwSyncGrnItemServices grnService = new VwSyncGrnItemServices(EmPromis);
			List<VwSyncGrnItem> lst = grnService.findByDocumentItemByUnit(company, project, documentNo, unitNumber, status);
			if(!CommonService.IsNull(lst)){
				return lst.get(0);
			}
			//else
			//	throw new Exception(" find not found Building,Phase,Zone under GRN Budget No = " + documentNo + " // unitNumber = " + unitNumber + " // status = " + status);
		}catch(Exception e){
			log.error(e.getMessage());
			System.err.println(e.getMessage());		
			throw e;
		}
		return null;
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 2:42:37 PM 
	 * CreateBy=Chatree Siangklom
	 * SupplierDebitNote
	 * TODO
	 * @param company
	 * @param project
	 * @param syncExternalRefNo
	 * @return
	 * @throws Exception
	 */
	private SupplierDebitNote ExistsApprovedCheck(String company, String project, String syncExternalRefNo) throws Exception 
	{
		try{
			SupplierDebitNoteServices SupplierDebitNoteServ = new SupplierDebitNoteServices(EmPromis);
			List<SupplierDebitNote> lst = SupplierDebitNoteServ.findBySapLivDocNo(company, project, syncExternalRefNo, "A");
			if(!CommonService.IsNull(lst)){
				return CommonService.getSize(lst) == 1 ? lst.get(0) : null;
			}
			
			//else 
			//	throw new Exception(" find not found  Liv Document No : " + syncExternalRefNo);
					
		}catch(Exception e){
			log.error(e.getMessage());
			System.err.println(e.getMessage());
			throw e;
		}
		return null;
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 2:42:39 PM 
	 * CreateBy=Chatree Siangklom
	 * SupplierDebitNote
	 * TODO
	 * @param value
	 * @return
	 * @throws Exception
	 */
	private SupplierDebitNote setHeader(MmSuppdebitnotesinb value) throws Exception {
		SupplierDebitNote model = null;		
		try{
			AccountPayableVoucherServices AccountPayableVoucherServ = new AccountPayableVoucherServices(EmPromis);
			
			//Find Project Master
			CostProject costProject = new ProjectServices(this.EmPromis).findByPlantCode(value.getSapPlantCode());
			String company = costProject.getId().getCompany();
			String project = costProject.getId().getProject();
			
			model =  ExistsApprovedCheck(company, project, value.getExtRefNo());
			if(model == null){
				if(value.getStatus().equals("A")){
					model = new SupplierDebitNote();				
					SupplierDebitNotePK id = new SupplierDebitNotePK();
					id.setCompany(company);
					id.setProject(project);
					id.setDebitNoteType(value.getVoucherType());
					
					//Create New PO
					String series_no = AccountPayableVoucherServ.getPromisSeriesNext(company, project, value.getVoucherType(), CommonService.ConvertPmsStagingDateTime(value.getDocDate()));					
					System.out.println("New series_no : " + series_no);					
					
					Long document_no = AccountPayableVoucherServ.getPromisDocumentNoNext(company, project, value.getVoucherType(), series_no); 
					System.out.println("New document_no : " + document_no);
					
					
					id.setDebitNoteNo(document_no);
					id.setDebitNoteSeries(series_no);
					
					model.setId(id);	
				}else
					throw new Exception("CN Document No = "+ value.getExtRefNo() +" is Status = "+ value.getStatus() +" fail.");
			}else
			{
				if(value.getStatus().equals("A"))
					throw new Exception("CN Document No = "+ value.getExtRefNo() +" Status = "+ value.getStatus() +" is Duplicate.");
					
			}
			model.setInstallmentFlag("Y");
			model.setSupplierReferenceDate(CommonService.ConvertStringToDateTime(value.getDocDate()));
			model.setCurrency("THB");
			model.setSapLivDoc(value.getLivDoc());
			model.setSupplierReference(value.getSupplierReference());
			model.setDebitNoteDate(CommonService.ConvertStringToDateTime(value.getDocDate()));
			model.setSupplierCode(value.getSupplierCode());
			
			model.setNetAmountLocal(CommonService.ConvertStringToBigDecimal(value.getNetAmount()));
			model.setTaxType(value.getTaxType());
			model.setDebitAmount(CommonService.ConvertStringToBigDecimal(value.getCostOfGoodsLocal()));
			model.setVatAmount(CommonService.ConvertStringToBigDecimal(value.getVatAmountH()));
			
			model.setSyncExternalRefNo(value.getExtRefNo());
			model.setSyncExternalRefKey(value.getExtRefKey());
			model.setSyncIssync("Y");
			model.setStatus(value.getStatus());
			
			//Create By 
			model.setCreatedBy(value.getCreatedBy());
			model.setCreatedOn(CommonService.ConvertStringToDateTime(value.getCreatedOn()));
			model.setLastUpdBy(value.getUpdatedBy());
			model.setLastUpdOn(CommonService.ConvertStringToDateTime(value.getUpdatedOn()));					
			if(!CommonService.IsNull(value.getUpdatedBy())){
				model.setLastUpdBy(value.getCreatedBy());
				model.setLastUpdOn(CommonService.ConvertStringToDateTime(value.getCreatedOn()));
			}
			
		}catch(Exception e){
			System.out.println(e.getMessage());
			log.equals(e.getMessage());
			throw e;
		}
		return model;
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 2:42:43 PM 
	 * CreateBy=Chatree Siangklom
	 * void
	 * TODO
	 * @param value_lst
	 * @throws Exception
	 */
	public void CreateToPromis(List<MmSuppdebitnotesinb> value_lst) throws Exception{
		try{
			
			SupplierDebitNoteServices SupplierDebitNoteServ = new SupplierDebitNoteServices(EmPromis);
			
			if(!CommonService.IsNull(value_lst)){
				
				SupplierDebitNote header = setHeader(value_lst.get(0));
				List<SupplierDbnoteBudget> detail_lst = new ArrayList<SupplierDbnoteBudget>();
				
				int seq = 0;
				for(MmSuppdebitnotesinb value : value_lst){
					seq++;
										
					SupplierDbnoteBudgetPK id = new SupplierDbnoteBudgetPK();
					id.setCompany(header.getId().getCompany()); 				//log.info("header.getId().getCompany() = " + header.getId().getCompany());
					id.setProject(header.getId().getProject());					//log.info("header.getId().getProject() = " + header.getId().getProject());
					id.setDebitNoteType(header.getId().getDebitNoteType());		//log.info("header.getId().getDebitNoteType() = " + header.getId().getDebitNoteType());
					id.setDebitNoteSeries(header.getId().getDebitNoteSeries());	//log.info("header.getId().getDebitNoteSeries() = " + header.getId().getDebitNoteSeries());
					id.setDebitNoteNo(header.getId().getDebitNoteNo());			//log.info("header.getId().getDebitNoteNo() = " + header.getId().getDebitNoteNo());
					id.setSeq(Long.parseLong(seq+""));							//log.info("seq = " + seq);
					//Check Exist DebitNote Budget
					SupplierDbnoteBudget detail = new SupplierDbnoteBudgetServices(EmPromis).findById(id);
					if(detail == null){
						//New Object
						System.out.println("New Object set value ");						
						detail = new SupplierDbnoteBudget();
						detail.setId(id);
					}
					
					System.out.println("After New Object set value");
					VwSyncGrnItem vwSyncGrnItem = FindGRNBudgetByUnit(detail.getId().getCompany(), detail.getId().getProject(), value.getRefNo(), value.getUnitNumber(), "A");
					if(vwSyncGrnItem != null){
						detail.setBuildingPhase(vwSyncGrnItem.getBuildingPhase());
						detail.setZoneCode(vwSyncGrnItem.getZoneCode());						
					}
					
					detail.setUnitNumber(value.getUnitNumber());
					
					//find Purchase Order 
					PurchaseModel purchaseModel = findPurchase(header.getId().getCompany(), header.getId().getProject(), value.getExtPoNo());
					detail.setPoType(purchaseModel.getPoType());
					detail.setPoSeries(purchaseModel.getPoSeries());
					detail.setPoNo(purchaseModel.getPoNo());
					detail.setPoRevision(purchaseModel.getRevisionNo());
					detail.setPoSeq(CommonService.ConvertStringToBigDecimal(value.getExtPoSeq()));
					
					//find Grn by Document No
					List<VwSyncGrnItem> grn_lst = new VwSyncGrnItemServices(EmPromis).findByDocumentNoWithoutSyncIsstaging(header.getId().getCompany(), header.getId().getProject(), value.getRefNo(), "A");
					if(!CommonService.IsNull(grn_lst)){
						VwSyncGrnItem goodsReceived = grn_lst.get(0);
						detail.setGrnType(goodsReceived.getGrnType());
						detail.setGrnSeries(goodsReceived.getGrnSeries());
						detail.setGrnNo(CommonService.ConvertStringToBigDecimal(goodsReceived.getGrnNo()));						
						detail.setGrnSeqNo(CommonService.ConvertStringToBigDecimal(value.getGrnSeqNo()));											
					}
					
					detail.setItemCode(value.getItemCode());
					detail.setQuantity(CommonService.ConvertStringToBigDecimal(value.getQuantity()));
					detail.setDbnoteAmount(CommonService.ConvertStringToBigDecimal(value.getCostBfVat()));
					detail.setNetDbnoteAmount(CommonService.ConvertStringToBigDecimal(value.getTotalCost()));
					
					//Interface Std SyncExternalRefNo  ---------------------------------------------------------------------------------
					detail.setSyncExternalRefNo(value.getExtRefNo());
					detail.setSyncExternalRefKey(value.getExtRefKey());
					detail.setSyncIssync("Y");
					detail.setStatus(value.getStatus());
					
					//Create By 
					detail.setCreatedBy(value.getCreatedBy());
					detail.setCreatedOn(CommonService.ConvertStringToDateTime(value.getCreatedOn()));
					detail.setLastUpdBy(value.getUpdatedBy());
					detail.setLastUpdOn(CommonService.ConvertStringToDateTime(value.getUpdatedOn()));					
					if(!CommonService.IsNull(value.getUpdatedBy())){
						detail.setLastUpdBy(value.getCreatedBy());
						detail.setLastUpdOn(CommonService.ConvertStringToDateTime(value.getCreatedOn()));
					}
					//END -------------------------------------------------------------------------------------------------------------------
					
					detail_lst.add(detail);
				}

				System.out.println("Start Save.");
				if(header.getStatus().equals("A")) //INSERT
					SupplierDebitNoteServ.SaveH_D(header, detail_lst, true);
				else if(header.getStatus().equals("Z")) //UPDATE
					SupplierDebitNoteServ.SaveH_D(header, detail_lst, false);
				
				//Update back to Staging
				List<MmSuppdebitnotesinb> vlauelist = new ArrayList<MmSuppdebitnotesinb>();
				for(MmSuppdebitnotesinb obj : value_lst)
				{
					obj.setPms57DocumentNo(header.getId().getDebitNoteType() + "-" + 
											header.getId().getDebitNoteSeries() + "-" + 
											Utilitys.leftPad(header.getId().getDebitNoteNo() + "", 6, "0"));
					obj.setPms57DebitNoteType(header.getId().getDebitNoteType());
					obj.setPms57DebitNoteSeries(header.getId().getDebitNoteSeries());
					obj.setPms57DebitNoteNo(header.getId().getDebitNoteNo());
					
					obj.setIssync("Y");
					obj.setMsgCode("S");
					obj.setMsgDesc("Success");
					obj.setMsgActResult(null);
					vlauelist.add(obj);	
				}
				MmSuppdebitnotesinbServ.Updated(vlauelist);
			}else 
				throw new Exception("List<MmSuppdebitnotesinb> List is Nothing.");
			
		}catch(Exception e)
		{
			try{
				if(!CommonService.IsNull(value_lst))
				{
					List<MmSuppdebitnotesinb> MmSuppdebitnotesinb_lst = new ArrayList<MmSuppdebitnotesinb>();
					for(MmSuppdebitnotesinb obj : value_lst)
					{
						obj.setIssync("Y");
						obj.setMsgCode("E");
						obj.setMsgDesc(CommonService.CutString(e.getMessage(), 3500));
						obj.setMsgActResult(CommonService.CutString(e.getMessage(), 3500));
						
						MmSuppdebitnotesinb_lst.add(obj);
					}
					MmSuppdebitnotesinbServ.Updated(MmSuppdebitnotesinb_lst);
				}
			}catch(Exception ex){
				System.out.println(ex.getMessage());
				log.error(ex.getMessage());
				throw ex;
			}
			
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			throw e;
		}
	}
	
//	
//	public static void main(String args[]){
//		try{
//			
//			SupplierDebitNoteServicesAction oo = new SupplierDebitNoteServicesAction();
//			
//			List<MmSuppdebitnotesinb> value_lst = MmSuppdebitnotesinbServ.findByInstanceNo("10000000000000006535");			
//			
//			oo.CreateToPromis(value_lst);
//			
//			
//		}catch(Exception e){
//			log.error(e.getMessage());
//			System.err.println(e.getMessage());
//		}
//	}

}
